package cn.edu.wynu.apis.jdbc;

import java.sql.*;

public class JDBCLearn {
    // 数据库连接信息
    private static final String URL = "jdbc:mysql://localhost:3306/jdbc_learn?useSSL=false&serverTimezone=UTC";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";
    
    public static void main(String[] args) {
        System.out.println("=== JDBC学习程序启动 ===");
        
        // 测试数据库连接
        testConnection();
        
        // 执行各种JDBC操作示例
        executeExamples();
        
        System.out.println("\n=== JDBC学习程序结束 ===");
    }
    
    /**
     * 测试数据库连接
     */
    public static void testConnection() {
        Connection connection = null;
        try {
            // 1. 加载驱动（MySQL 8.x自动注册，此步可省略）
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // 2. 建立连接
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            
            // 3. 检查连接是否成功
            if (connection != null) {
                System.out.println("✅ 数据库连接成功！");
                
                // 获取数据库元数据
                DatabaseMetaData metaData = connection.getMetaData();
                System.out.println("数据库名称: " + metaData.getDatabaseProductName());
                System.out.println("数据库版本: " + metaData.getDatabaseProductVersion());
            }
            
        } catch (ClassNotFoundException e) {
            System.out.println("❌ 找不到驱动类: " + e.getMessage());
        } catch (SQLException e) {
            System.out.println("❌ 数据库连接失败: " + e.getMessage());
        } finally {
            // 4. 关闭连接
            if (connection != null) {
                try {
                    connection.close();
                    System.out.println("🔒 数据库连接已关闭");
                } catch (SQLException e) {
                    System.out.println("关闭连接时出错: " + e.getMessage());
                }
            }
        }
    }
    
    /**
     * 执行各种JDBC操作示例
     */
    public static void executeExamples() {
        System.out.println("\n=== JDBC操作示例 ===");
        
        // 示例1：查询所有学生
        queryAllStudents();
        
        // 示例2：添加新学生
        addNewStudent();
        
        // 示例3：更新学生信息
        updateStudent();
        
        // 示例4：删除学生
        deleteStudent();
        
        // 示例5：使用PreparedStatement防止SQL注入
        queryStudentByName();
        
        // 示例6：事务处理
        transactionExample();
    }
    
    /**
     * 示例1：查询所有学生
     */
    public static void queryAllStudents() {
        System.out.println("\n📋 示例1：查询所有学生");
        
        String sql = "SELECT id, name, age, grade, created_time FROM students";
        
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            System.out.println("ID\t姓名\t年龄\t专业\t创建时间");
            System.out.println("-".repeat(50));
            
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String grade = rs.getString("grade");
                Timestamp createdTime = rs.getTimestamp("created_time");
                
                System.out.printf("%d\t%s\t%d\t%s\t%s\n", 
                    id, name, age, grade, createdTime);
            }
            
        } catch (SQLException e) {
            System.out.println("查询失败: " + e.getMessage());
        }
    }
    
    /**
     * 示例2：添加新学生
     */
    public static void addNewStudent() {
        System.out.println("\n➕ 示例2：添加新学生");
        
        String sql = "INSERT INTO students (name, age, grade) VALUES (?, ?, ?)";
        
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            // 设置参数
            pstmt.setString(1, "赵六");
            pstmt.setInt(2, 23);
            pstmt.setString(3, "人工智能");
            
            // 执行插入
            int affectedRows = pstmt.executeUpdate();
            
            if (affectedRows > 0) {
                System.out.println("✅ 成功添加新学生");
            }
            
        } catch (SQLException e) {
            System.out.println("添加失败: " + e.getMessage());
        }
    }
    
    /**
     * 示例3：更新学生信息
     */
    public static void updateStudent() {
        System.out.println("\n✏️ 示例3：更新学生信息");
        
        String sql = "UPDATE students SET age = ? WHERE name = ?";
        
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, 24);
            pstmt.setString(2, "赵六");
            
            int affectedRows = pstmt.executeUpdate();
            
            if (affectedRows > 0) {
                System.out.println("✅ 成功更新学生信息");
            }
            
        } catch (SQLException e) {
            System.out.println("更新失败: " + e.getMessage());
        }
    }
    
    /**
     * 示例4：删除学生
     */
    public static void deleteStudent() {
        System.out.println("\n🗑️ 示例4：删除学生");
        
        String sql = "DELETE FROM students WHERE name = ?";
        
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, "赵六");
            
            int affectedRows = pstmt.executeUpdate();
            
            if (affectedRows > 0) {
                System.out.println("✅ 成功删除学生");
            }
            
        } catch (SQLException e) {
            System.out.println("删除失败: " + e.getMessage());
        }
    }
    
    /**
     * 示例5：使用PreparedStatement防止SQL注入
     */
    public static void queryStudentByName() {
        System.out.println("\n🔒 示例5：安全查询（防止SQL注入）");
        
        String studentName = "张三"; // 可以替换为用户输入
        String sql = "SELECT * FROM students WHERE name = ?";
        
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, studentName);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("找到学生: %s, 年龄: %d, 专业: %s\n",
                        rs.getString("name"), rs.getInt("age"), rs.getString("grade"));
                }
            }
            
        } catch (SQLException e) {
            System.out.println("查询失败: " + e.getMessage());
        }
    }
    
    /**
     * 示例6：事务处理
     */
    public static void transactionExample() {
        System.out.println("\n💳 示例6：事务处理");
        
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            
            // 关闭自动提交，开启事务
            conn.setAutoCommit(false);
            
            // 执行多个操作
            String sql1 = "INSERT INTO students (name, age, grade) VALUES (?, ?, ?)";
            PreparedStatement pstmt1 = conn.prepareStatement(sql1);
            pstmt1.setString(1, "钱七");
            pstmt1.setInt(2, 25);
            pstmt1.setString(3, "网络安全");
            pstmt1.executeUpdate();
            
            String sql2 = "UPDATE students SET age = ? WHERE name = ?";
            PreparedStatement pstmt2 = conn.prepareStatement(sql2);
            pstmt2.setInt(1, 26);
            pstmt2.setString(2, "钱七");
            pstmt2.executeUpdate();
            
            // 提交事务
            conn.commit();
            System.out.println("✅ 事务执行成功");
            
        } catch (SQLException e) {
            // 回滚事务
            if (conn != null) {
                try {
                    conn.rollback();
                    System.out.println("❌ 事务执行失败，已回滚");
                } catch (SQLException ex) {
                    System.out.println("回滚失败: " + ex.getMessage());
                }
            }
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true); // 恢复自动提交
                    conn.close();
                } catch (SQLException e) {
                    System.out.println("关闭连接时出错: " + e.getMessage());
                }
            }
        }
    }
}
